package com.meet.dao;

import com.meet.entity.BoardRoom;
import com.meet.entity.Department;
import com.meet.entity.PageBean;
import com.meet.util.JDBCutil;
import com.meet.vo.BoardRoomTimeVo;

import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author RicardoZeng
 * @date 2022/8/11 20:54
 */
public class BoardRoomDaoImpl implements BoardRoomDao {
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;

    @Override
    public List<BoardRoom> getAllRooms(int currentPage, int rows) throws SQLException {
        List<BoardRoom> lists = new ArrayList<BoardRoom>();

        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "select * from boardroom limit ?,?";
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1,currentPage);
        preparedStatement.setObject(2,rows);

        resultSet = preparedStatement.executeQuery();

        while(resultSet.next()){
            BoardRoom boardRoom = new BoardRoom();

            boardRoom.setId(resultSet.getInt(1));
            boardRoom.setBoardRoomName(resultSet.getString(2));
            boardRoom.setMaxNumber(resultSet.getInt(3));
            boardRoom.setState(resultSet.getBoolean(4));

            lists.add(boardRoom);
        }

        JDBCutil.closeConnection();
        return lists;
    }

    @Override
    public int addBoardRoom(BoardRoom boardRoom) throws SQLException {
        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "insert into boardroom(r_name,number,state) values (?,?,?)";
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1,boardRoom.getBoardRoomName());
        preparedStatement.setObject(2,boardRoom.getMaxNumber());
        preparedStatement.setObject(3,boardRoom.getState());

        int count = preparedStatement.executeUpdate();

        JDBCutil.closeConnection();
        return count;
    }

    @Override
    public int deleteBoardRoom(Integer Id) throws SQLException {
        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "delete  from boardroom where id = ?";
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1,Id);

        int count = preparedStatement.executeUpdate();


        JDBCutil.closeConnection();
        return count;
    }

    @Override
    public int updateBoardRoom(BoardRoom boardRoom) throws SQLException {
        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "update boardRoom set r_name=?,number=?,state=? where id=?";
        //String sql = "update boardRoom set state=?";
        preparedStatement = conn.prepareStatement(sql);

        preparedStatement.setObject(1,boardRoom.getBoardRoomName());
        preparedStatement.setObject(2,boardRoom.getMaxNumber());
        preparedStatement.setObject(3,boardRoom.getState());
        preparedStatement.setObject(4,boardRoom.getId());

        //preparedStatement.setObject(1,boardRoom.getState());

        int count = preparedStatement.executeUpdate();

        JDBCutil.closeConnection();
        return count;
    }

    @Override
    public int count() throws SQLException {
        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "select count(1) from boardroom";
        preparedStatement = conn.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        int count = 0;
        if (resultSet.next()) {
            count = resultSet.getInt(1);
        }
        JDBCutil.closeConnection();
        return count;
    }

    @Override
    public BoardRoom getRoom(Integer Id) throws SQLException {
        BoardRoom boardRoom = new BoardRoom();
        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "select * from boardroom where id = ?";
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1,Id);

        resultSet = preparedStatement.executeQuery();

        while(resultSet.next()){

            boardRoom.setId(resultSet.getInt(1));
            boardRoom.setBoardRoomName(resultSet.getString(2));
            boardRoom.setMaxNumber(resultSet.getInt(3));
            boardRoom.setState(resultSet.getBoolean(4));

        }

        JDBCutil.closeConnection();
        return boardRoom;
    }

    /**
     * 李滇阳写的
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public List<BoardRoomTimeVo> getBoardRoomTime(Integer id) throws SQLException {
        List<BoardRoomTimeVo> list = new ArrayList<BoardRoomTimeVo>();
        Connection connection = JDBCutil.getConnection();
        PreparedStatement preparedStatement=connection.prepareStatement("select distinct(m.t_start),r.id, r.r_name, m.t_end from meeting m, boardroom r where m.r_id=r.id and r.id=?");
        preparedStatement.setObject(1,id);

        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            BoardRoomTimeVo boardRoom = new BoardRoomTimeVo();
            boardRoom.setId(resultSet.getInt("id"));
            boardRoom.setName(resultSet.getString("r_name"));
            boardRoom.setTstart(resultSet.getTimestamp("t_start"));
            boardRoom.setTend(resultSet.getTimestamp("t_end"));
            list.add(boardRoom);
        }
        resultSet.close();
        JDBCutil.closeConnection();
        return list;
    }



}
